package com.example.pdfexcelconverter.service.impl;

import com.example.pdfexcelconverter.service.PdfToJsonService;
import org.apache.pdfbox.pdmodel.PDDocument;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import technology.tabula.ObjectExtractor;
import technology.tabula.Page;
import technology.tabula.RectangularTextContainer;
import technology.tabula.Table;
import technology.tabula.extractors.SpreadsheetExtractionAlgorithm;
import com.fasterxml.jackson.databind.ObjectMapper;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.List;

@Service
public class PdfToJsonServiceImpl implements PdfToJsonService {
    public byte[] convertPdfToJson(byte[] pdfBytes) throws IOException {
        // 创建Excel工作簿
        Workbook workbook = new XSSFWorkbook();
        Map<String, Map<String, String>> allData = new LinkedHashMap<>(); // 存储所有Sheet数据

        // 使用PDFBox加载PDF
        try (PDDocument pdfDocument = PDDocument.load(new ByteArrayInputStream(pdfBytes))) {
            int totalPages = pdfDocument.getNumberOfPages();

            // 使用Tabula提取表格数据
            ObjectExtractor extractor = new ObjectExtractor(pdfDocument);
            SpreadsheetExtractionAlgorithm algorithm = new SpreadsheetExtractionAlgorithm();

            for (int pageNumber = 1; pageNumber <= totalPages; pageNumber++) {
                Sheet sheet = workbook.createSheet("Page_" + pageNumber);
                Page page = extractor.extract(pageNumber);
                List<Table> tables = algorithm.extract(page);

                Map<String, String> pageData = new LinkedHashMap<>(); // 单页数据
                for (Table table : tables) {
                    List<List<RectangularTextContainer>> rows = table.getRows();
                    for (int rowIdx = 0; rowIdx < rows.size(); rowIdx++) {
                        List<RectangularTextContainer> cells = rows.get(rowIdx);
                        for (int colIdx = 0; colIdx < cells.size(); colIdx++) {
                            RectangularTextContainer cell = cells.get(colIdx);
                            String cellText = cell.getText().trim();

                            // 生成Excel标准坐标（如A1, B2）
                            String excelColumn = convertToExcelColumn(colIdx);
                            String coordinate = excelColumn + (rowIdx + 1);
                            pageData.put(coordinate, cellText);
                        }
                    }
                }

                allData.put("Page" + pageNumber, pageData); // 合并到总数据
            }
        }

        // 生成JSON对象
        ObjectMapper mapper = new ObjectMapper();
        String jsonString = mapper.writerWithDefaultPrettyPrinter().writeValueAsString(allData);

        // 将JSON写入Excel元数据工作表
        Sheet metadataSheet = workbook.createSheet("Metadata");
        Row header = metadataSheet.createRow(0);
        header.createCell(0).setCellValue("SheetName");
        header.createCell(1).setCellValue("Coordinate");
        header.createCell(2).setCellValue("Value");
        int metaRowIndex = 1;
        for (Map.Entry<String, Map<String, String>> entry : allData.entrySet()) {
            String sheetName = entry.getKey();
            for (Map.Entry<String, String> cellEntry : entry.getValue().entrySet()) {
                Row metaRow = metadataSheet.createRow(metaRowIndex++);
                metaRow.createCell(0).setCellValue(sheetName);
                metaRow.createCell(1).setCellValue(cellEntry.getKey());
                metaRow.createCell(2).setCellValue(cellEntry.getValue());
            }
        }

        // 生成Excel文件并转换为字节数组
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        // 返回包含JSON数据的字节数组
        return jsonString.getBytes();
    }

    // 将列索引转换为Excel列字母（0->A, 1->B, ..., 26->AA）
    private static String convertToExcelColumn(int columnIndex) {
        StringBuilder column = new StringBuilder();
        while (columnIndex >= 0) {
            int remainder = columnIndex % 26;
            column.insert(0, (char) ('A' + remainder));
            columnIndex = (columnIndex / 26) - 1;
        }
        return column.toString();
    }

}
